package com.hvtt.web.connection;

import java.io.IOException;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.dbutils.BeanProcessor;

public class WEBConnection implements Serializable{

    private static WEBConnection webConnection = null;
    private static BeanProcessor beanProcessor;
    private String messageError = "";
    private int codeError = 0;

    /**
     * @return the beanProcessor
     */
    public static BeanProcessor getBeanProcessor() {
        if (beanProcessor == null) {
            beanProcessor = new BeanProcessor();
        }
        return beanProcessor;
    }

    /**
     * @param aBeanProcessor the beanProcessor to set
     */
    public static void setBeanProcessor(BeanProcessor aBeanProcessor) {
        beanProcessor = aBeanProcessor;
    }

    public static WEBConnection getInstance() {
        if (webConnection == null) {
            webConnection = new WEBConnection();
        }
        return webConnection;
    }

    @SuppressWarnings("CallToThreadDumpStack")
    public static Connection getConnection() {
        Connection con;
        try {
            con = DatasourceConnection.getConnection();
        } catch (Exception e) {
            con = null;
            e.printStackTrace();
        }
        return con;
    }

    @SuppressWarnings("UnusedAssignment")
    public void releaseConnection(ResultSet rs, PreparedStatement pt, CallableStatement call, Connection conn) throws SQLException {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlex) {
                sqlex.printStackTrace();
            }

            rs = null;
        }
        if (call != null) {
            try {
                call.close();
            } catch (SQLException sqlex) {
                sqlex.printStackTrace();
            }

            call = null;
        }

        if (pt != null) {
            try {
                pt.close();
            } catch (SQLException sqlex) {
                sqlex.printStackTrace();
            }

            pt = null;
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException sqlex) {
                sqlex.printStackTrace();
            }
            conn = null;
        }

    }

    /**
     * @author Nguyễn hoàng danh
     * @param sql ,command(sqlText or sqlStoredProcedure),Collection params -
     * query mysql
     * @category fetching data from mysql
     * @return ResultSet
     */
    @SuppressWarnings({"null", "ConstantConditions"})
    public List executeQuery(String sql, Command command,
            WEBParamCollection paramCollection, Class type) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pt = null;
        CallableStatement call = null;
        List result = null;
        Connection conn = null;
        try {
            conn = getConnection();
            switch (command) {
                case sqlText:
                    pt = conn.prepareStatement(sql);
                    int j = 0;
                    for (Object objParam : paramCollection) {
                        j = j + 1;
                        pt.setObject(j, objParam);
                    }
                    rs = pt.executeQuery();
                    break;
                case sqlStoredProcedure:
                    call = conn.prepareCall(sql);
                    int k = 0;
                    for (Object objParam : paramCollection) {
                        k = k + 1;
                        call.setObject(k, objParam);
                    }
                    rs = call.executeQuery();
                    break;
            }
            result = getBeanProcessor().toBeanList(rs, type);
        } finally {
            this.releaseConnection(rs, pt, call, conn);
        }
        return result==null?new ArrayList():result;
    }

    public ResultSet executeQuery1(String sql, Command command,
            WEBParamCollection paramCollection) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pt = null;
        CallableStatement call = null;
        List result = null;
        Connection conn = null;

        try {
            conn = getConnection();
            switch (command) {
                case sqlText:
                    pt = conn.prepareStatement(sql);
                    int j = 0;
                    for (Object objParam : paramCollection) {
                        j = j + 1;
                        pt.setObject(j, objParam);
                    }
                    rs = pt.executeQuery();
                    break;
                case sqlStoredProcedure:
                    call = conn.prepareCall(sql);
                    int k = 0;
                    for (Object objParam : paramCollection) {
                        k = k + 1;
                        call.setObject(k, objParam);
                    }
                    rs = call.executeQuery();
                    break;
            }
//            result = getBeanProcessor().toBeanList(rs, type);
        } finally {
//            this.releaseConnection(rs, pt, call, conn);
        }
        return rs;
    }

    public void executeUpdate(String sql) throws IOException {
        PreparedStatement pt = null;
        CallableStatement call = null;
        Connection conn = null;
        Statement sm = null;
        try {
            conn = getConnection();
            sm = conn.createStatement();
            sm.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (sm != null) {
                    sm.close();
                }
                this.releaseConnection(null, pt, call, conn);
            } catch (SQLException ex) {
                Logger.getLogger(WEBConnection.class.getName()).log(Level.SEVERE, "Error release connection.Please check connection for next actions!!", ex);
            }
        }
    }

    /**
     * @author Nguyễn Hoàng Danh
     * @param sql ,command(sqlText or sqlStoredProcedure),Collection params -
     * query mysql
     * @category update data from mysql
     * @return ResultSet
     */
    public boolean executeUpdate(String sql, Command command,
            WEBParamCollection paramCollection) {
        boolean bUpdate = false;
        PreparedStatement pt = null;
        CallableStatement call = null;
        Connection conn = null;
        Savepoint savepoint = null;
        try {
            conn = getConnection();
            this.beginTransaction(conn);
            savepoint = conn.setSavepoint(UUID.randomUUID().toString());
            switch (command) {
                case sqlText:
                    pt = conn.prepareStatement(sql);
                    int j = 0;
                    for (Object objParam : paramCollection) {
                        j = j + 1;
                        pt.setObject(j, objParam);
                    }
                    pt.execute();
                    bUpdate = true;
                    break;
                case sqlStoredProcedure:
                    call = conn.prepareCall(sql);
                    int k = 0;
                    for (Object objParam : paramCollection) {
                        k = k + 1;
                        call.setObject(k, objParam);
                    }
                    
                    call.execute();
                    bUpdate = true;
                    break;
            }
            this.endTransaction(conn);
        } catch (SQLException e) {
            this.setCodeError(e.getErrorCode());
            this.setMessageError("Update exception -->> " + e.getMessage());
            bUpdate = false;
            this.roolBackData(conn, savepoint);
        } finally {
            try {
                this.releaseConnection(null, pt, call, conn);
            } catch (SQLException ex) {
                Logger.getLogger(WEBConnection.class.getName()).log(Level.SEVERE, "Error release connection.Please check connection for next actions!!", ex);
            }
        }

        return bUpdate;
    }

    /**
     * @author Nguyễn hoàng danh
     * @param sql
     * @category fetching data from mysql with sqlText
     * @return ResultSet
     */
    public List executeQuery(String sql, Class type) throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pt = null;
        List list = null;
        try {
            conn = getConnection();
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            list = getBeanProcessor().toBeanList(rs, type);
        } finally {
            this.releaseConnection(rs, pt, null, conn);
        }
        return list;
    }

    public ResultSet executeQuery1(String sql) throws SQLException {

        Connection conn = getConnection();
        PreparedStatement pt = conn.prepareStatement(sql);
        ResultSet rs = pt.executeQuery();

        return rs;
    }

    public void beginTransaction(Connection conn) {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void endTransaction(Connection conn) {
        try {
            if (conn != null) {
                conn.commit();
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void roolBackData(Connection conn, Savepoint savepoint) {
        try {
            if (conn != null) {
                conn.rollback(savepoint);
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            this.setMessageError("Rollback exception -->> " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * @return the messageError
     */
    public String getMessageError() {
        return messageError;
    }

    /**
     * @param messageError the messageError to set
     */
    public void setMessageError(String messageError) {
        this.messageError = messageError;
    }

    /**
     * @return the codeError
     */
    public int getCodeError() {
        return codeError;
    }

    /**
     * @param codeError the codeError to set
     */
    public void setCodeError(int codeError) {
        this.codeError = codeError;
    }
}
